package com.dayuanit.booksystem;

import com.dayuanit.booksystem.util.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DataBaseUtil {

    private static Connection connection;

    private static final String jdbcUrl = JDBCUtils.getUrl();
    private static final String username = JDBCUtils.getUsername();
    private static final String password = JDBCUtils.getPassword();

    static {
        try {
            connection = DriverManager.getConnection(jdbcUrl, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 统一修改语句
     * @param sql
     */
    public static void modify(String sql) {
        System.out.println(sql);
        try (Statement statement = connection.createStatement()) {
            int rows = statement.executeUpdate(sql);
            System.out.println("影响行数:" + rows);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Publisher queryPublisher(String sql) {
        System.out.println(sql);
        Publisher publisher = null;
        try (Statement statement = connection.createStatement()) {
            ResultSet rs = statement.executeQuery(sql);

            while (rs.next()) {
                long publisherId = rs.getLong("publisher_id");
                String publisherName = rs.getString("publisher_name");
                String address = rs.getString("publisher_address");
                publisher = new Publisher(publisherId, publisherName, address);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return publisher;
    }

    public static List<Book> listBooks(String sql) {
        List<Book> list = new ArrayList<>();
        try (Statement statement = connection.createStatement()) {
            ResultSet rs = statement.executeQuery(sql);
            while (rs.next()) {
                long bookId = rs.getLong("book_id");
                String bookName = rs.getString("book_name");
                String author = rs.getString("author");
                String price = rs.getString("price");
                long publisherId = rs.getLong("publisher_id");

                Book book = new Book(bookId, bookName, author, price, publisherId);
                list.add(book);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 查询单个书籍
     * @param sql
     * @return
     */
    public static Book queryBook(String sql) {
        Book book = null;
        List<Book> books = listBooks(sql);
        if (!books.isEmpty()) {
            book = books.get(0);
        }
        return book;
    }

    /**
     * 统一count语句
     * @param sql
     * @return
     */
    public static int count(String sql) {
        System.out.println(sql);
        try (Statement statement = connection.createStatement()) {
            ResultSet rs = statement.executeQuery(sql);
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }
}
